Create PROCEDURE [dbo].[ChangeGoodsCode]
    @Old_code Varchar(20),
    @New_code varchar(20),
    @output tinyint output
AS
Declare @TableName Varchar(50)
Declare @columnName Varchar(20)
Declare @ExecCommand varchar(500)

begin try
    begin TRAN
    IF EXISTS
    (
        SELECT id
        FROM sys.sysobjects
        WHERE name = 'Price_Sazman'
              AND xtype = 'U'
    )
        Delete from Price_Sazman
        Where K_Code in (
                            Select K_Code from KalaId Where Darou_Flag <> 6 AND K_Code = @Old_code
                        )

    update kalaid
    set k_code = @New_code
    where K_Code = @Old_code
    set @Old_code = '''' + @Old_code + ''''
    set @New_code = '''' + @New_code + ''''
    Declare ChangeCodeValue cursor For
    SELECT SCHEMA_NAME(SysOb.schema_id) + '.' + SysOb.name AS TableName,
           SysCol.name AS ColumnName
    FROM sys.objects AS SysOb
        INNER JOIN sys.columns AS SysCol
            ON SysOb.object_id = SysCol.object_id
    WHERE SysOb.type = 'U'
          AND SysCol.name IN ( 'k_code', 'GoodCode', 'GoodsCode', 'Kala_Code', 'StockCode', 'K_Code_H', 'K_Code_M' )
          AND SysCol.name <> 'Kardeks'
    ORDER BY TableName,
             ColumnName;

    Open ChangeCodeValue
    Fetch Next From ChangeCodeValue
    Into @TableName,
         @columnName
    While @@Fetch_Status = 0
    Begin
        set @ExecCommand
            = ' Update ' + @TableName + '  Set ' + @columnName + '=' + @New_code + ' where ' + @columnName + '='
              + @Old_code
        Exec (@ExecCommand)

        Fetch Next From ChangeCodeValue
        Into @TableName,
             @columnName
    End
    Close ChangeCodeValue
    Deallocate ChangeCodeValue
    Set @output = 1
    commit tran
    return @output
end try
begin catch
    IF CURSOR_STATUS('global', 'ChangeCodeValue') = 1
    BEGIN
        CLOSE ChangeCodeValue
        DEALLOCATE ChangeCodeValue
    END

    DECLARE @ErrorMessage NVARCHAR(2048) = NULL,
            @ErrorSeverity INT,
            @ErrorState INT;
    SELECT @ErrorMessage = ERROR_MESSAGE() + ' Line ' + CAST(ERROR_LINE() AS NVARCHAR(5)),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();
    Set @output = 0
    ROLLBACK TRANSACTION;
    RAISERROR(@ErrorMessage, 16, 1);
    return @output
end catch

